/**
 * Title:        A database helper class to hide the sql
 * Description:  <p>
 * @version 1.0
 */

package com.timber.login;

import java.sql.* ;
import java.util.* ;
import java.io.* ;

import com.timber.util.*;

public class SLUserDBSQL
{
	/////////////////////////////////////////////////////////////////////
	// Returns a list of components
	/////////////////////////////////////////////////////////////////////
	public static Vector getSysUserComponent( Connection conn,
							Integer suId, String componentType, String userID ) throws SQLException {

		System.out.println( "suId " + suId ) ;
		System.out.println( "type " + componentType ) ;
		System.out.println( "user " + userID ) ;

		Vector val = new Vector() ;

		CallableStatement cs = conn.prepareCall("{? = call common..p_GetSysUserComponent ?,?, ?}");
		if ( suId == null )
			cs.setNull( 2, Types.INTEGER ) ;
		else
			cs.setInt( 2, suId.intValue() ) ;

		if ( componentType == null )
			cs.setNull( 3, Types.VARCHAR ) ;
		else
			cs.setString( 3, componentType ) ;

		if ( userID == null )
			cs.setNull( 4, Types.VARCHAR ) ;
		else
			cs.setString( 4, userID ) ;

		ResultSet rs = cs.executeQuery() ;

		while( rs.next() ) {
			Integer scId = new Integer( rs.getInt( 1 ) ) ;
			String type = rs.getString( 2 ) ;
			String component = rs.getString( 3 ) ;
			val.add( new SysUserComponent( scId, type, component ) ) ;
		}

		cs.close() ;
		return val ;
	}

	/////////////////////////////////////////////////////////////////////
	// Returns primary groups for a user
	/////////////////////////////////////////////////////////////////////
	public static Vector getPrimaryGroups( Connection conn,
							String userID ) throws SQLException {
		Vector v = new Vector() ;

		PreparedStatement ps = conn.prepareStatement(
			"select gu.UserLogin "
			+ "from common..t_SysUser u, common..t_SysGroupUser g, common..t_SysUser gu "
			+ "where u.UserLogin = ? "
			+ "and u.SuId = g.SuId "
			+ "and gu.SuId = g.GroupId " ) ;

		ps.setString( 1, userID ) ;

		ResultSet rs = ps.executeQuery() ;

        while( rs.next() ) {
            String group = rs.getString( "UserLogin" ) ;
			v.add( group ) ;
		}

		return v ;
	}

    public static Vector getAllUsers( Connection conn ) throws SQLException {
		Vector v = new Vector() ;

		PreparedStatement ps = conn.prepareStatement(
			"select UserLogin from common..t_SysUser where Type = ? " ) ;

		ps.setString( 1, "U" ) ;

		ResultSet rs = ps.executeQuery() ;

        while( rs.next() ) {
            String name = rs.getString( "UserLogin" ) ;
			v.add( name ) ;
		}

        return v ;
    }

    public static Vector getAllGroups( Connection conn ) throws SQLException {
		Vector v = new Vector() ;

		PreparedStatement ps = conn.prepareStatement(
			"select UserLogin from common..t_SysUser where Type = ? " ) ;

		ps.setString( 1, "G" ) ;

		ResultSet rs = ps.executeQuery() ;

        while( rs.next() ) {
            String name = rs.getString( "UserLogin" ) ;
			v.add( name ) ;
		}

        return v ;
    }

    public static boolean isUserInGroup( Connection conn,
		java.lang.String username, java.lang.String groupname)
            throws SQLException
            //, NoSuchUserException, NoSuchGroupException - not thrown => commented
            {

		Vector v = new Vector() ;

		PreparedStatement ps = conn.prepareStatement(
			"select u.UserLogin "
			+ "from common..t_SysUser u, common..t_SysGroupUser g, common..t_SysUser gu "
			+ "where gu.UserLogin = ? "
			+ "and u.SuId = g.SuId "
			+ "and gu.SuId = g.GroupId "
			+ "and u.UserLogin = ? " ) ;

		ps.setString( 1, groupname ) ;
		ps.setString( 2, username ) ;

		ResultSet rs = ps.executeQuery() ;

		if ( rs.next() )
            return true ;

		return false ;

    }

    public static Vector getUserNamesFromGroup( Connection conn,
		String groupname)
                throws SQLException
                //, NoSuchGroupException  - not thrown => commented
                {
		Vector v = new Vector() ;

		PreparedStatement ps = conn.prepareStatement(
			"select u.UserLogin "
			+ "from common..t_SysUser u, common..t_SysGroupUser g, common..t_SysUser gu "
			+ "where gu.UserLogin = ? "
			+ "and u.SuId = g.SuId "
			+ "and gu.SuId = g.GroupId " ) ;

		ps.setString( 1, groupname ) ;

		ResultSet rs = ps.executeQuery() ;

        while( rs.next() ) {
            String name = rs.getString( "UserLogin" ) ;
			v.add( name ) ;
		}

        return v ;
    }

    public static void changePassword(Connection conn, String oldPassword, String newPassword)
        throws SQLException, SLPasswordException {
                                                                 // DAW 13Jul2004

        SLUserDBSQL.changePassword(conn, oldPassword, newPassword, false );

      }

      public static void changePassword(Connection conn, String oldPassword, String newPassword, boolean validatePassword )
          throws SQLException, SLPasswordException {


      // DAW 13Jul2004 Additional parameter to disable password validation

                CallableStatement cs =
                  conn.prepareCall("exec common..p_SysPassword ?,?,?,?,?,?,?");

                cs.setString(1, oldPassword);
                cs.setString(2, newPassword);
                cs.setInt(3, 0);
                cs.setNull(4, java.sql.Types.INTEGER);
                cs.setNull(5, java.sql.Types.VARCHAR);
                cs.setInt(6, 1);                 // DAW 13Jul2004

                if ( validatePassword )
                  cs.setString(7, "Y");         // DAW 13Jul2004
                else
                  cs.setString(7, "N");         // DAW 13Jul2004


                cs.registerOutParameter(3, java.sql.Types.INTEGER);
                cs.registerOutParameter(4, java.sql.Types.INTEGER);
                cs.registerOutParameter(5, java.sql.Types.VARCHAR);

                cs.execute();

                int exitCode = cs.getInt(3);
                int errCode = cs.getInt(4);
                String msg = cs.getString(5);

                cs.close();

                if(exitCode!=1) {
                  throw new SLPasswordException(errCode,msg);
                }

      }

  public static String verifyLogin(Connection conn) throws SQLException, SLLoginException {
      CallableStatement cs = conn.prepareCall("exec common..p_SysLogin ?,?,?,?");

      cs.setInt(1, 0);
      cs.setNull(2, java.sql.Types.DATE);
      cs.setNull(3, java.sql.Types.INTEGER);
      cs.setNull(4, java.sql.Types.VARCHAR);

      cs.registerOutParameter(1, java.sql.Types.INTEGER);
      cs.registerOutParameter(2, java.sql.Types.DATE);
      cs.registerOutParameter(3, java.sql.Types.INTEGER);
      cs.registerOutParameter(4, java.sql.Types.VARCHAR);

      cs.execute();

      int ok = cs.getInt(1);
      java.sql.Date lldate= cs.getDate(2);
      String lastLogin = null;
      if( lldate != null )
	lastLogin = cs.getDate(2).toString();

      int errCode = cs.getInt(3);
      String msg = cs.getString(4);

      cs.close();

      if(ok!=1)
        throw new SLLoginException(errCode,msg);
      return lastLogin;
  }

  public static String verifyLogin(Connection conn, boolean singleSession) throws SQLException, SLLoginException {

      if (!singleSession)
        return verifyLogin(conn);

      CallableStatement cs = conn.prepareCall("exec common..p_SysLogin ?,?,?,?,?");

      cs.setInt(1, 0);
      cs.setNull(2, java.sql.Types.DATE);
      cs.setNull(3, java.sql.Types.INTEGER);
      cs.setNull(4, java.sql.Types.VARCHAR);
      cs.setInt(5, 1);

      cs.registerOutParameter(1, java.sql.Types.INTEGER);
      cs.registerOutParameter(2, java.sql.Types.DATE);
      cs.registerOutParameter(3, java.sql.Types.INTEGER);
      cs.registerOutParameter(4, java.sql.Types.VARCHAR);

      cs.execute();

      int ok = cs.getInt(1);
      java.sql.Date lldate= cs.getDate(2);
      String lastLogin = null;
      if( lldate != null )
	lastLogin = cs.getDate(2).toString();
      int errCode = cs.getInt(3);
      String msg = cs.getString(4);

      cs.close();

      if(ok!=1)
        throw new SLLoginException(errCode,msg);
      return lastLogin;
  }


}

